package com.controller;

import com.bean.*;
import com.util.BaseDao;
import com.util.CustomDateConverter;
import javafx.beans.property.SimpleObjectProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.control.cell.TextFieldTableCell;
import javafx.util.converter.IntegerStringConverter;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalTime;


import static com.Main.changeUI;

public class SelectArevise {
    @FXML
    private TableView<PriWoker> tableViewP;
    @FXML
    private TableView<Visitor> VisitorTable;
    @FXML
    private TableView<Worker>tableViewW;

    @FXML
    private TextField pwField;
    @FXML
    private TextField wFeild;
    @FXML
    private TextField vField;




    @FXML
    private TableColumn<Worker,String > w_no;
    @FXML
    private TableColumn<Worker,String >w_name;
    @FXML
    private TableColumn<Worker,String >w_sex;
    @FXML
    private TableColumn<Worker,String >w_id;
    @FXML
    private TableColumn<Worker, LocalDate>w_birth;
    @FXML
    private TableColumn<Worker,String >w_work;
    @FXML
    private TableColumn<Worker,String >w_postion;
    @FXML
    private TableColumn<Worker, Integer>w_wages;


    @FXML
    private TableColumn<Visitor,String >v_no;
    @FXML
    private TableColumn<Visitor,String >v_name;
    @FXML
    private TableColumn<Visitor,LocalDate >v_date;
    @FXML
    private TableColumn<Visitor,String >v_pno;
    @FXML
    private TableColumn<Visitor,String>v_wno;

    @FXML
    private TableColumn<PriWoker,String >p_no;
    @FXML
    private TableColumn<PriWoker,String >p_name;
    @FXML
    private TableColumn<PriWoker,String >p_sex;
    @FXML
    private TableColumn<PriWoker,String >p_id;
    @FXML
    private TableColumn<PriWoker,LocalDate>p_birth;
    @FXML
    private TableColumn<PriWoker,String >p_crime;
    @FXML
    private TableColumn<PriWoker,LocalDate >p_intime;
    @FXML
    private TableColumn<PriWoker,LocalDate >p_outtime;
    @FXML
    private TableColumn<PriWoker, String>p_postion;
    @FXML
    private TableColumn<PriWoker,String >p_work;
    @FXML
    private TableColumn<PriWoker,String >p_wpostion;
    @FXML
    private TableColumn<PriWoker,String >p_wtime;


    private ObservableList<PriWoker> pwitems;
    private ObservableList<Worker> witems;
    private ObservableList<Visitor> vitems;
    @FXML
    private Button pPush;
    @FXML
    private Button vPush;
    @FXML
    private Button wPush;



    public void initialize(){
        Pinitialize();

      //  PWinitialize();

        Winitialize();

        Vinitialize();

     //   Pselect.setVisible(false);
     //   Wselect.setVisible(false);
     //   Vselect.setVisible(false);

        pPush.setVisible(false);
        wPush.setVisible(false);
        vPush.setVisible(false);








    }
    public void Pinitialize() {
        //接受数据库信息
        pwitems = FXCollections.observableArrayList();
       // pitems2 = FXCollections.observableArrayList();
        if (pwField.getText() == null || pwField.getText().trim().isEmpty()) {
            // pwField是空的，或者只包含空白字符
            loadPrisonersData();
        } else {
            // pwField不为空
            loadPriwokerDate();
        }


        //信息显示在View1中
        tableViewP.setItems(pwitems);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        //showPTable();
      //  tableView1.setVisible(true);

        setupTableViewW();
      //  for (Prisoner prisoner : pitems) {
         //  prisoner.setSelected(false); // 设置默认未选中
      //  }

    }
    public void Winitialize() {
        //接受数据库信息
        witems = FXCollections.observableArrayList();
        // pitems2 = FXCollections.observableArrayList();
        if (wFeild.getText() == null || wFeild.getText().trim().isEmpty()) {
            // pwField是空的，或者只包含空白字符
            loadWDataFromDatabase();
        } else {
            // pwField不为空
            loadWorkDataFromDatabase();
        }


        //信息显示在View1中
        tableViewW.setItems(witems);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        //showPTable();
        //  tableView1.setVisible(true);

        setupWTableViewW();
        //  for (Prisoner prisoner : pitems) {
        //  prisoner.setSelected(false); // 设置默认未选中
        //  }

    }
    public void Vinitialize(){
        //接受数据库信息
        vitems = FXCollections.observableArrayList();
        // pitems2 = FXCollections.observableArrayList();
        if (vField.getText() == null ||vField.getText().trim().isEmpty()) {
            // pwField是空的，或者只包含空白字符
            loadVDataFromDatabase();
        } else {
            // pwField不为空
            loadVisitDataFromDatabase();
        }


        //信息显示在View1中
        VisitorTable.setItems(vitems);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        //showPTable();
        //  tableView1.setVisible(true);

        setupVTableViewW();
        //  for (Prisoner prisoner : pitems) {
        //  prisoner.setSelected(false); // 设置默认未选中
        //  }


    }
    private void loadPriwokerDate(){
        pwitems.clear();
        String idNumber = pwField.getText().trim();
        String query = "SELECT " +
                "p.pno, p.pname, p.sex, p.idcard, p.birthday, p.crime, " +
                "p.intime, p.outtime, p.postion, pw.pworkname, " +
                "pw.pworkpostion, pw.wtime " +
                "FROM prisoner p " +
                "INNER JOIN Pwork pw ON p.pno = pw.pno " +
                "WHERE p.pno LIKE ?";

        try (Connection connection = BaseDao.getConn();
             PreparedStatement preparedStatement = connection.prepareStatement(query)) {

            preparedStatement.setString(1, idNumber + "%"); // 设置参数占位符的值

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    // 根据ResultSet创建PriWoker对象，并添加到列表中
                    PriWoker priWoker = new PriWoker(
                            resultSet.getString("pno"),
                            resultSet.getString("pname"),
                            resultSet.getString("sex"),
                            resultSet.getString("idcard"),
                            resultSet.getDate("birthday").toLocalDate(),
                            resultSet.getString("crime"),
                            resultSet.getDate("intime").toLocalDate(),
                            resultSet.getDate("outtime").toLocalDate(),
                            resultSet.getString("postion"),
                            resultSet.getString("pworkname"),
                            resultSet.getString("pworkpostion"),
                            resultSet.getString("wtime")
                    );
                    pwitems.add(priWoker);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 将查询结果设置到TableView的数据模型中
      //  tableViewP.setItems(pwitems);

    }
    private void loadPrisonersData() {
        pwitems.clear();
        Connection connection = null;
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接
            connection = BaseDao.getConn();// 从您的数据库连接工厂获取连接

                    // 调用存储过程
                    String storedProc = "{call pAllMessage()}";
            callableStatement = connection.prepareCall(storedProc);

            // 执行存储过程
            resultSet = callableStatement.executeQuery();

            // 处理结果集
            while (resultSet.next()) {
                // 假设囚犯表有以下列：pno, p_name, p_sex, p_id, p_birth, p_crime, p_intime, p_outtime, p_postion
                String pno = resultSet.getString("pno");
                String pname =resultSet.getString("pname");
                String sex = resultSet.getString("sex");
                String idcard = resultSet.getString("idcard");
                LocalDate birthday = resultSet.getDate("birthday").toLocalDate();// 转换SQL Date到LocalDate
                String crime = resultSet.getString("crime");
                LocalDate intime = resultSet.getDate("intime").toLocalDate();
                LocalDate outtime = resultSet.getDate("outtime").toLocalDate();
                String postion = resultSet.getString("postion");
                String pworkname = resultSet.getString("pworkname");
                String pworkpostion = resultSet.getString("pworkpostion");
                String wtime = resultSet.getString("wtime");

                pwitems.add(new PriWoker(pno,pname,sex,idcard,birthday,crime,intime,outtime,postion,pworkname,pworkpostion,wtime));
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (callableStatement != null) callableStatement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    private void loadWorkDataFromDatabase() {
        witems.clear();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = BaseDao.getConn();
            // 使用PreparedStatement来设置参数，提高安全性
            String sql = "SELECT * FROM worker WHERE wno LIKE ?";
            pstmt = conn.prepareStatement(sql);

            // 假设你有一个名为wnoVariable的变量，包含你要查询的wno值
            pstmt.setString(1, wFeild.getText() + "%"); // 设置查询参数

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                // 同上... 省略了字段获取和对象创建的代码
                String wno = rs.getString("wno");
                String wname = rs.getString("wname");
                String sex = rs.getString("sex");
                String idcard = rs.getString("idcard");
                Date sqlDate = rs.getDate("birthday");
                LocalDate birthday = null;
                if (sqlDate != null) {
                    birthday = sqlDate.toLocalDate();
                }
                String jobtype = rs.getString("jobtype");
                String postion = rs.getString("postion");
                Integer wagesProperty = 0;
                try {
                    int wageValue = rs.getInt("wages");

                    //   System.out.println(wageValue);
                    if (!rs.wasNull()) { // 检查结果是否不是 SQL NULL
                        wagesProperty = wageValue; // 将 int 值设置到 IntegerProperty
                    } else {
                        wagesProperty = 0; // 或者你可以选择设置一个默认值，比如 0
                    }
                } catch (SQLException e) {
                    // 处理可能的 SQLException
                    e.printStackTrace();
                }

                witems.add(new Worker(wno, wname, sex, idcard, birthday, jobtype, postion,  wagesProperty));
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库查询失败", e);
        } finally {
            // 关闭资源，避免潜在的资源泄露
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                // 处理关闭资源时可能发生的异常
                e.printStackTrace();
            }
        }
    }
    private void loadWDataFromDatabase() {
        witems.clear();
        Connection conn = null;
        try{
            conn = BaseDao.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM worker");
            while(rs.next()) {
                String wno = rs.getString("wno");
                String wname = rs.getString("wname");
                String sex = rs.getString("sex");
                String idcard = rs.getString("idcard");
                Date sqlDate = rs.getDate("birthday");
                LocalDate birthday = null;
                if (sqlDate != null) {
                    birthday = sqlDate.toLocalDate();
                }
                String jobtype = rs.getString("jobtype");
                String postion = rs.getString("postion");
                Integer wagesProperty = 0;
                try {
                    int wageValue = rs.getInt("wages");

                    //   System.out.println(wageValue);
                    if (!rs.wasNull()) { // 检查结果是否不是 SQL NULL
                        wagesProperty = wageValue; // 将 int 值设置到 IntegerProperty
                    } else {
                        wagesProperty = 0; // 或者你可以选择设置一个默认值，比如 0
                    }
                } catch (SQLException e) {
                    // 处理可能的 SQLException
                    e.printStackTrace();
                }

                witems.add(new Worker(wno, wname, sex, idcard, birthday, jobtype, postion,  wagesProperty));

            }
        }catch(SQLException e){
            throw new RuntimeException(e);
        }

    }

    private void loadVisitDataFromDatabase() {
        vitems.clear(); // 假设witems是用于存放查询结果的ObservableList
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = BaseDao.getConn();
            // 使用PreparedStatement来设置参数，提高安全性
            String sql = "SELECT * FROM visitor WHERE vno LIKE ?"; // 根据实际情况调整SQL语句
            pstmt = conn.prepareStatement(sql);

            // 假设你有一个名为vnoVariable的变量，包含你要查询的vno值
            // 这里我们使用vFeild.getText()来获取输入字段的文本
            pstmt.setString(1, vField.getText()+ "%"); // 设置查询参数

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                // 根据查询结果创建Visitor对象
                String vno = rs.getString("vno");
                String vname = rs.getString("vname");
                Date sqlDate = rs.getDate("vdate");
                LocalDate vdate = null;
                if(sqlDate != null){
                    vdate = sqlDate.toLocalDate();
                }
                String pno = rs.getString("pno");

                String wno = rs.getString("wno");

                vitems.add(new Visitor(vno,  vname,  vdate, pno, wno ));
            }
        } catch (SQLException e) {
            // 同时在控制台打印异常信息
            e.printStackTrace();
        } finally {
            // 关闭资源，避免潜在的资源泄露
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                // 处理关闭资源时可能发生的异常
                e.printStackTrace();
            }
        }
    }
    private void loadVDataFromDatabase(){
        vitems.clear();
        // IntegerProperty wagesProperty = new SimpleIntegerProperty();
        Connection conn = null;
        try{
            conn = BaseDao.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM visitor");
            while(rs.next()){
                String vno = rs.getString("vno");
                String vname = rs.getString("vname");
                Date sqlDate = rs.getDate("vdate");
                LocalDate vdate = null;
                if(sqlDate != null){
                    vdate = sqlDate.toLocalDate();
                }
                String pno = rs.getString("pno");

                String wno = rs.getString("wno");

                vitems.add(new Visitor(vno,  vname,  vdate, pno, wno ));

            }
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
    private void setupWTableViewW() {
        // 假设 Worker 类中包含相应的属性和 getter 方法
        TableColumn<Worker, String>  wnoColumn = (TableColumn<Worker, String>) tableViewW.getColumns().get(0);
        wnoColumn.setCellValueFactory(new PropertyValueFactory<>("wno"));
        //  p_noColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        //  p_noColumn.setOnEditCommit(event -> {
        //当单元格编辑完成后，更新Worker对象的w_no属性
        //PriWoker priWoker = event.getRowValue();
        //  priWoker.setPno(event.getNewValue());
        // );


        // 对于可编辑的列，您可以使用以下模式设置 Cell Factory
        TableColumn<Worker, String> wnameColumn = (TableColumn<Worker, String>) tableViewW.getColumns().get(1);
        wnameColumn.setCellValueFactory(new PropertyValueFactory<>("wname"));
        wnameColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        wnameColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setWname(event.getNewValue());
        });

        // 为编辑提交设置事件处理器
        TableColumn<Worker, String> wsexColumn = (TableColumn<Worker, String>) tableViewW.getColumns().get(2);
        wsexColumn.setCellValueFactory(new PropertyValueFactory<>("sex"));
        wsexColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        wsexColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setSex(event.getNewValue());
        });

        TableColumn<Worker, String> widColumn = (TableColumn<Worker, String>) tableViewW.getColumns().get(3);
        widColumn.setCellValueFactory(new PropertyValueFactory<>("idcard"));
        widColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        widColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setIdcard(event.getNewValue());
        });

        TableColumn<Worker, LocalDate> birthdayColumn = (TableColumn<Worker, LocalDate>) tableViewW.getColumns().get(4);
        birthdayColumn.setCellValueFactory(param -> new SimpleObjectProperty<>(param.getValue().getBirthday()));
        CustomDateConverter converter = new CustomDateConverter();
        birthdayColumn.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        birthdayColumn.setOnEditCommit(event -> {
            Worker worker = event.getRowValue();
            LocalDate newBirthDate = converter.fromString(event.getNewValue().toString());
            worker.setBirthday(newBirthDate);
        });


        TableColumn<Worker, String> jobtypeColumn = (TableColumn<Worker, String>) tableViewW.getColumns().get(5);
        jobtypeColumn.setCellValueFactory(new PropertyValueFactory<>("jobtype"));
        jobtypeColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        jobtypeColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setJobtype(event.getNewValue());
        });
        // 如果您希望某些列在编辑后立即更新数据库，您可以在 setOnEditCommit 中添加数据库更新逻辑
        TableColumn<Worker, String> postionColumn = (TableColumn<Worker, String>) tableViewW.getColumns().get(6);
        postionColumn.setCellValueFactory(new PropertyValueFactory<>("postion"));
        postionColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        postionColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setPostion(event.getNewValue());
        });

        TableColumn<Worker, Integer> wagesColumn = (TableColumn<Worker, Integer>) tableViewW.getColumns().get(7);
        wagesColumn.setCellValueFactory(new PropertyValueFactory<>("wages"));
        wagesColumn.setCellFactory(TextFieldTableCell.forTableColumn(new IntegerStringConverter())); // 使用数字转换器
        wagesColumn.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的wages属性
            Worker worker = event.getRowValue();
            worker.setWages(event.getNewValue()); // 假设setWages方法接受Integer类型的参数
        });





    }

    private void setupVTableViewW() {
        // 假设 Worker 类中包含相应的属性和 getter 方法
        TableColumn<Visitor, String>  vnoColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(0);
        vnoColumn.setCellValueFactory(new PropertyValueFactory<>("vno"));
        //  p_noColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        //  p_noColumn.setOnEditCommit(event -> {
        //当单元格编辑完成后，更新Worker对象的w_no属性
        //PriWoker priWoker = event.getRowValue();
        //  priWoker.setPno(event.getNewValue());
        // );


        // 对于可编辑的列，您可以使用以下模式设置 Cell Factory
        TableColumn<Visitor, String> vnameColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(1);
        vnameColumn.setCellValueFactory(new PropertyValueFactory<>("vname"));
        vnameColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        vnameColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setVname(event.getNewValue());
        });

        TableColumn<Visitor, LocalDate> vdateColumn = (TableColumn<Visitor, LocalDate>) VisitorTable.getColumns().get(2);
        vdateColumn.setCellValueFactory(param -> new SimpleObjectProperty<>(param.getValue().getVadate()));
        CustomDateConverter converter = new CustomDateConverter();
        vdateColumn.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        vdateColumn.setOnEditCommit(event -> {
            Visitor visitor = event.getRowValue();
            LocalDate newVdate = converter.fromString(event.getNewValue().toString());
            visitor.setVadate(newVdate);
        });


        TableColumn<Visitor, String> pnoColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(3);
        pnoColumn.setCellValueFactory(new PropertyValueFactory<>("pno"));
        pnoColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        pnoColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setPno(event.getNewValue());
        });
        // 如果您希望某些列在编辑后立即更新数据库，您可以在 setOnEditCommit 中添加数据库更新逻辑
        TableColumn<Visitor, String> wnoColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(4);
        wnoColumn.setCellValueFactory(new PropertyValueFactory<>("wno"));
        wnoColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        wnoColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setWno(event.getNewValue());
        });


    }
    // 假设您已经在您的控制器类中初始化了 tableViewW

    // 为 tableViewW 中的每个 TableColumn 设置 Cell Value Factory 和 Cell Factory
    private void setupTableViewW() {
        // 假设 Worker 类中包含相应的属性和 getter 方法
        TableColumn<PriWoker, String> p_noColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(0);
        p_noColumn.setCellValueFactory(new PropertyValueFactory<>("pno"));
      //  p_noColumn.setCellFactory(TextFieldTableCell.forTableColumn());
      //  p_noColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            //PriWoker priWoker = event.getRowValue();
          //  priWoker.setPno(event.getNewValue());
       // );


        // 对于可编辑的列，您可以使用以下模式设置 Cell Factory
        TableColumn<PriWoker, String> p_nameColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(1);
        p_nameColumn.setCellValueFactory(new PropertyValueFactory<>("pname"));
        p_nameColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_nameColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setPname(event.getNewValue());
        });

        // 为编辑提交设置事件处理器
        TableColumn<PriWoker, String> p_sexColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(2);
        p_sexColumn.setCellValueFactory(new PropertyValueFactory<>("sex"));
        p_sexColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_sexColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setSex(event.getNewValue());
        });

        TableColumn<PriWoker, String> p_idColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(3);
        p_idColumn.setCellValueFactory(new PropertyValueFactory<>("idcard"));
        p_idColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_idColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setIdcard(event.getNewValue());
        });

        TableColumn<PriWoker, LocalDate> birthdayColumn = (TableColumn<PriWoker, LocalDate>) tableViewP.getColumns().get(4);
        birthdayColumn.setCellValueFactory(param -> new SimpleObjectProperty<>(param.getValue().getBirthdayTime()));
        CustomDateConverter converter = new CustomDateConverter();
        birthdayColumn.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        birthdayColumn.setOnEditCommit(event -> {
            PriWoker priWoker = event.getRowValue();
            LocalDate newBirthDate = converter.fromString(event.getNewValue().toString());
            priWoker.setBirthdayTime(newBirthDate);
        });


        TableColumn<PriWoker, String> p_crimeColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(5);
        p_crimeColumn.setCellValueFactory(new PropertyValueFactory<>("crime"));
        p_crimeColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_crimeColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setCrime(event.getNewValue());
        });
        // 如果您希望某些列在编辑后立即更新数据库，您可以在 setOnEditCommit 中添加数据库更新逻辑


        TableColumn<PriWoker, LocalDate> p_intimeColumn = (TableColumn<PriWoker, LocalDate>) tableViewP.getColumns().get(6);
        p_intimeColumn.setCellValueFactory(param -> new SimpleObjectProperty<>(param.getValue().getIntime()));
        p_intimeColumn.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        p_intimeColumn.setOnEditCommit(event -> {
            PriWoker priWoker = event.getRowValue();
            LocalDate newIntime = converter.fromString(event.getNewValue().toString());
            priWoker.setIntime(newIntime);
        });

        TableColumn<PriWoker, LocalDate> p_outtimeColumn = (TableColumn<PriWoker, LocalDate>) tableViewP.getColumns().get(7);
        p_outtimeColumn.setCellValueFactory(param -> new SimpleObjectProperty<>(param.getValue().getOuttime()));
        p_outtimeColumn.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        p_outtimeColumn.setOnEditCommit(event -> {
            PriWoker priWoker = event.getRowValue();
            LocalDate newOutime = converter.fromString(event.getNewValue().toString());
            priWoker.setOuttime(newOutime);
        });

        TableColumn<PriWoker, String> p_postionColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(8);
        p_postionColumn.setCellValueFactory(new PropertyValueFactory<>("postion"));
        p_postionColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_postionColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setPostion(event.getNewValue());
        });

        TableColumn<PriWoker, String> p_pworknameColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(9);
        p_pworknameColumn.setCellValueFactory(new PropertyValueFactory<>("pworkname"));
        p_pworknameColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_pworknameColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setPworkname(event.getNewValue());
        });

        TableColumn<PriWoker, String> p_pwpostioneColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(10);
        p_pwpostioneColumn.setCellValueFactory(new PropertyValueFactory<>("pworkposion"));
        p_pwpostioneColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_pwpostioneColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setPworkposion(event.getNewValue());
        });

        TableColumn<PriWoker, String> p_wtimeColumn = (TableColumn<PriWoker, String>) tableViewP.getColumns().get(11);
        p_wtimeColumn.setCellValueFactory(new PropertyValueFactory<>("wtime"));
        p_wtimeColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        p_wtimeColumn.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            PriWoker priWoker = event.getRowValue();
            priWoker.setWtime(event.getNewValue());
        });


    }

    // 在您的 initialize 方法中调用 setup 方法


// 确保在 fxml 文件中正确设置了控制器和 fx:id，以便这些变量可以在控制器中被识别和使用

    public void doQuit(ActionEvent actionEvent) {
        changeUI("ui/MainInterFace.fxml");


    }
    public void doPmanage(ActionEvent actionEvent) {
        tableViewP.setVisible(true);
        VisitorTable.setVisible(false);
        tableViewW.setVisible(false);
        pPush.setVisible(true);
        wPush.setVisible(false);
        vPush.setVisible(false);
        if (pwField.getText() == null || pwField.getText().trim().isEmpty()) {
            // pwField是空的，或者只包含空白字符
            loadPrisonersData();
        } else {
            // pwField不为空
            loadPriwokerDate();
        }

    }

    public void doVmanage(ActionEvent actionEvent) {
        tableViewP.setVisible(false);
        VisitorTable.setVisible(true);
        tableViewW.setVisible(false);
        pPush.setVisible(false);
        wPush.setVisible(false);
        vPush.setVisible(true);
        if (vField.getText() == null || vField.getText().trim().isEmpty()) {
            // pwField是空的，或者只包含空白字符
            loadVDataFromDatabase();
        } else {
            // pwField不为空
            loadVisitDataFromDatabase();
        }
    }

    public void doWmanage(ActionEvent actionEvent) {
        tableViewP.setVisible(false);
        VisitorTable.setVisible(false);
        tableViewW.setVisible(true);
        pPush.setVisible(false);
        wPush.setVisible(true);
        vPush.setVisible(false);
        if (wFeild.getText() == null || wFeild.getText().trim().isEmpty()) {
            // pwField是空的，或者只包含空白字符
            loadWDataFromDatabase();
        } else {
            // pwField不为空
            loadWorkDataFromDatabase();
        }
    }
    @FXML
    public void doWpush(ActionEvent actionEvent) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = BaseDao.getConn(); // 假设这是你的数据库连接获取方法
            String sql = "UPDATE worker SET "
                    + "wname = ?, "
                    + "sex = ?, "
                    + "idcard = ?, "
                    + "birthday = ?, "
                    + "jobtype = ?, "
                    + "postion = ?, "
                    + "wages = ? "
                    + "WHERE wno = ?";

            pstmt = conn.prepareStatement(sql);

            for (Worker worker : witems) {
                int index = 1; // 参数索引从1开始
                pstmt.setString(index++, worker.getWname());
                pstmt.setString(index++, worker.getSex());
                pstmt.setString(index++, worker.getIdcard());
                // 将LocalDate转换为SQL的Date
                pstmt.setDate(index++, Date.valueOf(worker.getBirthday()));
                pstmt.setString(index++, worker.getJobtype());
                pstmt.setString(index++, worker.getPostion());
                pstmt.setInt(index++, worker.getWages());
                pstmt.setString(index, worker.getWno()); // 主键或唯一标识符

                pstmt.addBatch(); // 添加到批处理
            }

            pstmt.executeBatch(); // 执行批处理插入
            Alert alert = new Alert(Alert.AlertType.INFORMATION, "修改成功", ButtonType.OK);
            alert.showAndWait();
        } catch (SQLException e) {

            e.printStackTrace();
            Alert alert = new Alert(Alert.AlertType.ERROR, "修改失败", ButtonType.OK);
            alert.showAndWait();
            // 这里可以加入错误处理逻辑
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    @FXML
    public void doVpush(ActionEvent actionEvent) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = BaseDao.getConn(); // 假设这是你的数据库连接获取方法
            // 假设visitor表中的主键是vno，根据实际情况调整字段
            String sql = "UPDATE visitor SET "
                    + "vname = ?, "
                    + "vdate = ?, " // 假设日期字段是vdate
                    + "pno = ?, " // 假设有关联prisoner表的外键字段v_pno
                    + "wno = ? " // 假设有关联worker表的外键字段v_wno
                    + "WHERE vno = ?"; // 假设vno是visitor表的主键

            pstmt = conn.prepareStatement(sql);

            for (Visitor visitor : vitems) { // 假设vitems是存放Visitor对象的ObservableList
                int index = 1; // 参数索引从1开始
                pstmt.setString(index++, visitor.getVname());
                // 将LocalDate转换为SQL的Date
                pstmt.setDate(index++, Date.valueOf(visitor.getVadate()));
                pstmt.setString(index++, visitor.getPno());
                pstmt.setString(index++, visitor.getWno());
                pstmt.setString(index, visitor.getVno()); // 主键或唯一标识符

                pstmt.addBatch(); // 添加到批处理
            }

            pstmt.executeBatch(); // 执行批处理更新
            Alert alert = new Alert(Alert.AlertType.INFORMATION, "修改成功", ButtonType.OK);
            alert.showAndWait();
        } catch (SQLException e) {
            // 在控制台打印堆栈跟踪
            e.printStackTrace();
            // 显示错误信息的对话框
            Alert alert = new Alert(Alert.AlertType.ERROR, "修改失败: " + e.getMessage(), ButtonType.OK);
            alert.showAndWait();
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public void doPpush() {
        // 遍历pwitems中的每个PriWoker对象

            try {
                for (PriWoker priWoker : pwitems) {
                // 使用PriWoker对象的pno调用updatePrisoner存储过程
                updatePrisoner(priWoker.getPno(),priWoker.getPname(),priWoker.getSex(),
                        priWoker.getIdcard(),priWoker.getBirthdayTime(),priWoker.getCrime(),
                        priWoker.getIntime(),priWoker.getOuttime(),priWoker.getPostion());
                updatePwork(priWoker.getPworkname(), priWoker.getPno(),priWoker.getPworkposion(),
                        priWoker.getWtime(),priWoker.getIntime(),priWoker.getOuttime());
                // 如果也需要更新Pwork表，则使用相同的pno调用updatePwork存储过程
                // updatePwork(priWoker.getPno());
                }
                Alert alert = new Alert(Alert.AlertType.INFORMATION, "修改成功", ButtonType.OK);
                alert.showAndWait();
            } catch (SQLException e) {
                Alert alert = new Alert(Alert.AlertType.ERROR, "修改失败", ButtonType.OK);
                alert.showAndWait();
                e.printStackTrace();
                // 可以在这里添加错误处理逻辑，例如显示错误消息
            }


    }
    private void updatePrisoner(String pno,String pname,String sex, String idcard,
                                LocalDate birthday,String crime,LocalDate intime,
                                LocalDate outtime,String postion) throws SQLException {
        String storedProcPrisoner = "{call UpdatePrisoner(?,?,?,?,?,?,?,?,?)}";
        try (Connection connection = BaseDao.getConn();
             CallableStatement callableStatement = connection.prepareCall(storedProcPrisoner)) {
            // 设置存储过程的参数
            callableStatement.setString(1, pno);
            callableStatement.setString(2, pname);
            callableStatement.setString(3, sex);
            callableStatement.setString(4, idcard);
            callableStatement.setDate(5, Date.valueOf(birthday));
            callableStatement.setString(6, crime);
            callableStatement.setDate(7, Date.valueOf(intime));
            callableStatement.setDate(8, Date.valueOf(outtime));
            callableStatement.setString(9, postion);
            // 执行存储过程
            callableStatement.execute();

        }
    }
    private void updatePwork(String pworkname,String pno,String pworkpostion,
                             String wtime,LocalDate begintime,LocalDate outtime) throws SQLException {
        String storedProcPwork = "{call UpdatePwork(?,?,?,?,?,?)}";
        try (Connection connection = BaseDao.getConn();
             CallableStatement callableStatement = connection.prepareCall(storedProcPwork)) {
            callableStatement.setString(1, pworkname);
            callableStatement.setString(2, pno);
            callableStatement.setString(3, pworkpostion);
            callableStatement.setString(4, wtime);
            callableStatement.setDate(5, Date.valueOf(begintime));
            callableStatement.setDate(6, Date.valueOf(outtime));
            callableStatement.execute();
        }
    }
}
